import openpyxl
import pymysql
wb=openpyxl.load_workbook('bookstore.xlsx')
#创建一个用户入职信息的worksheet对象
userinfo_sheet=wb.create_sheet('user_info',index=-1)
wb.save('bookstore.xlsx')
data=[
    ['id','idcard','username','realname','pwd','telphone','email','age','sex','address','hiredate','sal','job','company'],
    [1, '123456789012345678', 'user1', 'Alice', 'pwd1234', '13800000000', 'alice@example.com', 25, 'Female', 'No.189, Garden Road', '2020-05-15', 8500.00, 'Engineer', 'TechCorp'],
    [2, '234567890123456789', 'user2', 'Bob', 'pwd5678', '13900000000', 'bob@example.com', 30, 'Male', 'No.190, Park Avenue', '2019-06-20', 9200.00, 'Manager', 'BusinessCorp'],
    [3, '345678901234567890', 'user3', 'Charlie', 'pwd9012', '13700000000', 'charlie@example.com', 22, 'Male', 'No.191, Lake Street', '2021-07-25', 7800.00, 'Analyst', 'DataCorp'],
    [4, '456789012345678901', 'user4', 'Diana', 'pwd3456', '13600000000', 'diana@example.com', 28, 'Female', 'No.192, Hill Road', '2018-08-30', 8800.00, 'Designer', 'DesignCorp'],
    [5, '567890123456789012', 'user5', 'Eve', 'pwd7890', '13500000000', 'eve@example.com', 24, 'Female', 'No.193, Forest Lane', '2022-09-10', 7500.00, 'Developer', 'DevCorp'],
    [6, '678901234567890123', 'user6', 'Frank', 'pwd1357', '13400000000', 'frank@example.com', 35, 'Male', 'No.194, River Side', '2017-10-15', 9500.00, 'Consultant', 'ConsultCorp'],
    [7, '789012345678901234', 'user7', 'Grace', 'pwd2468', '13300000000', 'grace@example.com', 29, 'Female', 'No.195, Mountain View', '2016-11-20', 8200.00, 'Engineer', 'TechCorp'],
    [8, '890123456789012345', 'user8', 'Hank', 'pwd1357', '13200000000', 'hank@example.com', 31, 'Male', 'No.196, Ocean Drive', '2015-12-25', 9000.00, 'Manager', 'BusinessCorp'],
    [9, '901234567890123456', 'user9', 'Ivy', 'pwd9876', '13100000000', 'ivy@example.com', 27, 'Female', 'No.197, Pine Street', '2014-01-30', 7600.00, 'Analyst', 'DataCorp'],
    [10, '012345678901234567', 'user10', 'Jack', 'pwd4321', '13000000000', 'jack@example.com', 33, 'Male', 'No.198, Maple Avenue', '2013-02-10', 8900.00, 'Designer', 'DesignCorp'],
    [11, '123456789012345678', 'user11', 'Kate', 'pwd1234', '12900000000', 'kate@example.com', 26, 'Female', 'No.199, Oak Road', '2012-03-15', 8400.00, 'Developer', 'DevCorp'],
    [12, '234567890123456789', 'user12', 'Luke', 'pwd5678', '12800000000', 'luke@example.com', 30, 'Male', 'No.200, Pine Street', '2011-04-20', 9300.00, 'Consultant', 'ConsultCorp'],
    [13, '345678901234567890', 'user13', 'Mona', 'pwd9012', '12700000000', 'mona@example.com', 23, 'Female', 'No.201, Cedar Lane', '2010-05-25', 7700.00, 'Engineer', 'TechCorp'],
    [14, '456789012345678901', 'user14', 'Nick', 'pwd3456', '12600000000', 'nick@example.com', 29, 'Male', 'No.202, Birch Road', '2009-06-30', 8700.00, 'Manager', 'BusinessCorp'],
    [15, '567890123456789012', 'user15', 'Olive', 'pwd7890', '12500000000', 'olive@example.com', 24, 'Female', 'No.203, Spruce Avenue', '2008-07-10', 7400.00, 'Analyst', 'DataCorp'],
    [16, '678901234567890123', 'user16', 'Peter', 'pwd1357', '12400000000', 'peter@example.com', 35, 'Male', 'No.204, Willow Street', '2007-08-15', 9400.00, 'Designer', 'DesignCorp'],
    [17, '789012345678901234', 'user17', 'Quinn', 'pwd2468', '12300000000', 'quinn@example.com', 28, 'Female', 'No.205, Elm Road', '2006-09-20', 8300.00, 'Developer', 'DevCorp'],
    [18, '890123456789012345', 'user18', 'Ryan', 'pwd1357', '12200000000', 'ryan@example.com', 32, 'Male', 'No.206, Oak Street', '2005-10-25', 9100.00, 'Consultant', 'ConsultCorp'],
    [19, '901234567890123456', 'user19', 'Sophia', 'pwd9876', '12100000000', 'sophia@example.com', 26, 'Female', 'No.207, Pine Avenue', '2004-11-30', 7900.00, 'Engineer', 'TechCorp'],
    [20, '012345678901234567', 'user20', 'Tom', 'pwd4321', '12000000000', 'tom@example.com', 34, 'Male', 'No.208, Maple Road', '2003-12-10', 8800.00, 'Manager', 'BusinessCorp'],
    [21, '123456789012345678', 'user21', 'Ursula', 'pwd1234', '11900000000', 'ursula@example.com', 27, 'Female', 'No.209, Oak Lane', '2002-01-15', 8500.00, 'Analyst', 'DataCorp'],
    [22, '234567890123456789', 'user22', 'Victor', 'pwd5678', '11800000000', 'victor@example.com', 31, 'Male', 'No.210, Pine Street', '2001-02-20', 9200.00, 'Designer', 'DesignCorp'],
    [23, '345678901234567890', 'user23', 'Wendy', 'pwd9012', '11700000000', 'wendy@example.com', 25, 'Female', 'No.211, Cedar Avenue', '2000-03-25', 7800.00, 'Developer', 'DevCorp'],
    [24, '456789012345678901', 'user24', 'Xavier', 'pwd3456', '11600000000', 'xavier@example.com', 29, 'Male', 'No.212, Birch Road', '1999-04-30', 8800.00, 'Consultant', 'ConsultCorp'],
    [25, '567890123456789012', 'user25', 'Yvonne', 'pwd7890', '11500000000', 'yvonne@example.com', 24, 'Female', 'No.213, Spruce Street', '1998-05-10', 7500.00, 'Engineer', 'TechCorp'],
    [26, '678901234567890123', 'user26', 'Zack', 'pwd1357', '11400000000', 'zack@example.com', 35, 'Male', 'No.214, Willow Avenue', '1997-06-15', 9500.00, 'Manager', 'BusinessCorp'],
    [27, '789012345678901234', 'user27', 'Ava', 'pwd2468', '11300000000', 'ava@example.com', 28, 'Female', 'No.215, Elm Road', '1996-07-20', 8200.00, 'Analyst', 'DataCorp'],
    [28, '890123456789012345', 'user28', 'Ben', 'pwd1357', '11200000000', 'ben@example.com', 32, 'Male', 'No.216, Oak Street', '1995-08-25', 9000.00, 'Designer', 'DesignCorp'],
    [29, '901234567890123456', 'user29', 'Cara', 'pwd9876', '11100000000', 'cara@example.com', 26, 'Female', 'No.217, Pine Avenue', '1994-09-30', 7600.00, 'Developer', 'DevCorp'],
    [30, '012345678901234567', 'user30', 'Derek', 'pwd4321', '11000000000', 'derek@example.com', 34, 'Male', 'No.218, Maple Road', '1993-10-10', 8900.00, 'Consultant', 'ConsultCorp'],
    [31, '123456789012345678', 'user31', 'Eva', 'pwd1234', '10900000000', 'eva@example.com', 27, 'Female', 'No.219, Oak Lane', '1992-11-15', 8400.00, 'Engineer', 'TechCorp'],
    [32, '234567890123456789', 'user32', 'Frank', 'pwd5678', '10800000000', 'frank@example.com', 31, 'Male', 'No.220, Pine Street', '1991-12-20', 9300.00, 'Manager', 'BusinessCorp'],
    [33, '345678901234567890', 'user33', 'Grace', 'pwd9012', '10700000000', 'grace@example.com', 23, 'Female', 'No.221, Cedar Avenue', '1990-01-25', 7700.00, 'Analyst', 'DataCorp'],
    [34, '456789012345678901', 'user34', 'Hank', 'pwd3456', '10600000000', 'hank@example.com', 29, 'Male', 'No.222, Birch Road', '1989-02-03', 8700.00, 'Designer', 'DesignCorp'],
    [35, '567890123456789012', 'user35', 'Ivy', 'pwd7890', '10500000000', 'ivy@example.com', 24, 'Female', 'No.223, Spruce Street', '1988-03-10', 7400.00, 'Developer', 'DevCorp'],
    [36, '678901234567890123', 'user36', 'Jack', 'pwd1357', '10400000000', 'jack@example.com', 35, 'Male', 'No.224, Willow Avenue', '1987-04-15', 9400.00, 'Consultant', 'ConsultCorp'],
    [37, '789012345678901234', 'user37', 'Kate', 'pwd2468', '10300000000', 'kate@example.com', 28, 'Female', 'No.225, Elm Road', '1986-05-20', 8300.00, 'Engineer', 'TechCorp'],
    [38, '890123456789012345', 'user38', 'Luke', 'pwd1357', '10200000000', 'luke@example.com', 32, 'Male', 'No.226, Oak Street', '1985-06-25', 9100.00, 'Manager', 'BusinessCorp'],
    [39, '901234567890123456', 'user39', 'Mona', 'pwd9876', '10100000000', 'mona@example.com', 26, 'Female', 'No.227, Pine Avenue', '1984-07-30', 7900.00, 'Developer', 'DevCorp'],
    [40, '012345678901234567', 'user40', 'Nick', 'pwd4321', '10000000000', 'nick@example.com', 34, 'Male', 'No.228, Maple Road', '1983-08-10', 8800.00, 'Consultant', 'ConsultCorp'],
    [41, '123456789012345678', 'user41', 'Olive', 'pwd1234', '9900000000', 'olive@example.com', 27, 'Female', 'No.229, Oak Lane', '1982-09-15', 8500.00, 'Analyst', 'DataCorp'],
    [42, '234567890123456789', 'user42', 'Peter', 'pwd5678', '9800000000', 'peter@example.com', 31, 'Male', 'No.230, Pine Street', '1981-10-20', 9200.00, 'Designer', 'DesignCorp'],
    [43, '345678901234567890', 'user43', 'Quinn', 'pwd9012', '9700000000', 'quinn@example.com', 25, 'Female', 'No.231, Cedar Avenue', '1980-11-25', 7800.00, 'Developer', 'DevCorp'],
    [44, '456789012345678901', 'user44', 'Ryan', 'pwd3456', '9600000000', 'ryan@example.com', 29, 'Male', 'No.232, Birch Road', '1979-12-30', 8800.00, 'Consultant', 'ConsultCorp'],
    [45, '567890123456789012', 'user45', 'Sophia', 'pwd7890', '9500000000', 'sophia@example.com', 24, 'Female', 'No.233, Spruce Street', '1978-01-10', 7500.00, 'Engineer', 'TechCorp'],
    [46, '678901234567890123', 'user46', 'Tom', 'pwd1357', '9400000000', 'tom@example.com', 35, 'Male', 'No.234, Willow Avenue', '1977-02-15', 9500.00, 'Manager', 'BusinessCorp'],
    [47, '789012345678901234', 'user47', 'Ursula', 'pwd2468', '9300000000', 'ursula@example.com', 28, 'Female', 'No.235, Elm Road', '1976-03-20', 8300.00, 'Analyst', 'DataCorp'],
    [48, '890123456789012345', 'user48', 'Victor', 'pwd1357', '9200000000', 'victor@example.com', 32, 'Male', 'No.236, Oak Street', '1975-04-25', 9100.00, 'Designer', 'DesignCorp'],
    [49, '901234567890123456', 'user49', 'Wendy', 'pwd9876', '9100000000', 'wendy@example.com', 26, 'Female', 'No.237, Pine Avenue', '1974-05-30', 7900.00, 'Developer', 'DevCorp'],
    [50, '012345678901234567', 'user50', 'Xavier', 'pwd4321', '9000000000', 'xavier@example.com', 34, 'Male', 'No.238, Maple Road', '1973-06-10', 8800.00, 'Consultant', 'ConsultCorp'],
    [51, '123456789012345678', 'user51', 'Yvonne', 'pwd1234', '8900000000', 'yvonne@example.com', 27, 'Female', 'No.239, Oak Lane', '1972-07-15', 8500.00, 'Engineer', 'TechCorp'],
    [52, '234567890123456789', 'user52', 'Zack', 'pwd5678', '8800000000', 'zack@example.com', 31, 'Male', 'No.240, Pine Street', '1971-08-20', 9200.00, 'Manager', 'BusinessCorp'],
    [53, '345678901234567890', 'user53', 'Ava', 'pwd9012', '8700000000', 'ava@example.com', 25, 'Female', 'No.241, Cedar Avenue', '1970-09-25', 7800.00, 'Developer', 'DevCorp'],
    [54, '456789012345678901', 'user54', 'Ben', 'pwd3456', '8600000000', 'ben@example.com', 29, 'Male', 'No.242, Birch Road', '1969-10-30', 8800.00, 'Consultant', 'ConsultCorp'],
    [55, '567890123456789012', 'user55', 'Cara', 'pwd7890', '8500000000', 'cara@example.com', 24, 'Female', 'No.243, Spruce Street', '1968-11-10', 7500.00, 'Engineer', 'TechCorp'],
    [56, '678901234567890123', 'user56', 'Derek', 'pwd1357', '8400000000', 'derek@example.com', 35, 'Male', 'No.244, Willow Avenue', '1967-12-15', 9500.00, 'Manager', 'BusinessCorp']
]
#插入到excel表格中
for row in data:
    userinfo_sheet.append(row)
wb.save('bookstore.xlsx')

#读取出来
wb=openpyxl.load_workbook('bookstore.xlsx')
#创建一个用户入职信息的worksheet对象
userinfo_sheet=wb['user_info']
for row in userinfo_sheet.iter_rows():
    for cell in row:
        print(cell.value,end='\t\t')
    print()

#插入到mysql数据库里
from DButils import DButils
from datetime import datetime
DB=DButils(
    host='localhost',
    port=3306,
    user='root',
    password='root',
    database='db_1',
    charset='utf8',
)
#获取最大行号
mr=userinfo_sheet.max_row
# row_range=userinfo_sheet[1:mr+1]
#excel表头行索引是1，跳过表头行索引从2开始

for row in userinfo_sheet.iter_rows(min_row=2,max_row=mr):
    item_row=[cell.value for cell in row]#item中存储一行的数据值，cell是一个对象
    if item_row:
        #一个个将列表中的值给修改类型
        item_row[0]=int(item_row[0])
        item_row[7]=int(item_row[7])

        # 转换为日期,只要运行了一次datatime.striptime()就已经把字符串转换为了datetime对象，再次运行就会报错，所以用之前先用if语句判断一下
        if isinstance(item_row[10], str):  # 检查是否为字符串
            item_row[10] = datetime.strptime(item_row[10], '%Y-%m-%d').date()  # 假设第10列是 hiredate (DATE 类型)
        elif isinstance(item_row[10], datetime):  # 如果已经是 datetime 对象
            item_row[10] = item_row[10].date()  # 直接取日期部分

        item_row[11]=float(item_row[11])

        sql="insert into userinfo values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
        DB.insert(sql,item_row)
    else:
        print("当前行为空")
#关闭数据库连接
DB.close()
wb.save("bookstore.xlsx")